package com.ld.hpfs.view.excel;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.servlet.view.document.AbstractExcelView;
import java.text.SimpleDateFormat;


public class DealExcelView extends AbstractExcelView{

	@Override
	protected void buildExcelDocument(Map<String, Object> model,
			HSSFWorkbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		  // // 取得模型数据
        // SqlRowSet table = (SqlRowSet) model.get("table");
        // String title = model.get("title").toString();

        // 取得模型数据
    	HashMap<String,Object> tableList = (HashMap<String,Object>) model.get("table");
//        String title = "本地科目表";
//
//    	String driverClassName="net.sourceforge.jtds.jdbc.Driver";
//    	String url="jdbc:jtds:sqlserver://192.168.100.100:1433;DatabaseName=HPTAXDB2005";
//    	
//    	Class.forName(driverClassName);
//    	Connection  dbConn = DriverManager.getConnection(url, "sa", "lordar@2012");
//
//    	Statement st = dbConn.createStatement();
    	String curCompanyCode=tableList.get("curCompanyCode").toString();
    	String invType=tableList.get("invType").toString();
    	SimpleDateFormat now = new SimpleDateFormat("yyyyMMddHHmmss");//设置日期格式
    	String fileName=curCompanyCode+"_"+invType+"_"+now.format(new Date());
    	response.setContentType("application/vnd.ms-excel");
    	response.setHeader("Content-disposition","attachment;filename="+fileName+".xls");
    	String[] name = (String[])tableList.get("columnName");//获得表头信息
    	List<HashMap<String,Object>> table= (List<HashMap<String,Object>>)tableList.get("list");//获得表中信息 	
//        // 创建工作表和标题
        HSSFSheet sheet = workbook.createSheet(); // 创建工作区
//        HSSFRow row_title = sheet.createRow(0); // 创建一行引用对象
//        HSSFFont title_font = workbook.createFont(); // 创建标题的字体
//
//        title_font.setFontHeightInPoints((short) 8);
//        title_font.setFontHeight((short) HSSFFont.BOLDWEIGHT_NORMAL);
//        title_font.setColor((short) (HSSFFont.COLOR_RED));
//
//        HSSFCellStyle title_style = workbook.createCellStyle(); // 创建样式
//        title_style.setFont(title_font);
//
//        HSSFCell cell_title = row_title.createCell(1); // 创建单元格引用对象
//        cell_title.setCellStyle(title_style);
//        cell_title.setCellValue(title);
        
        
        // 创建数据表头
        //String titles[] = { "发票种类", "单据号","单据日期","客户编号","客户名称","客户税号","客户地址电话","客户银行及帐号","备注","专用发票红票通知单号","普通发票红票对应正数发票代码","开票人","复核人","收款人","销方银行及帐号","销方地址电话","商品编号","商品名称","规格型号","计量单位","数量","金额（含税）","税率","税额","折扣金额(含税)", "折扣税额"};
        
//        String titles[] =new String[100];
//        int temp=0;
//        for (HashMap<String,Object> entry : table) {
//            entry.remove("ID_HEAD_ID");
//            entry.remove("COMPANY_ID");
//            entry.remove("DOC_STATUS");
//        	if(temp==0){
//        		Set set = entry.keySet();
//                Iterator it = set.iterator();
//                int index=0;
//                while(it.hasNext()){             	  
//             	  titles[index++]=it.next().toString();
//                }
//        	}
//        	 temp++;
//        }
        
        
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle items_style = workbook.createCellStyle();
        items_style.setAlignment((short) HSSFCellStyle.ALIGN_CENTER);

        HSSFFont celltbnamefont = workbook.createFont();
        celltbnamefont.setFontHeightInPoints((short) 10);

        celltbnamefont.setColor((short) (HSSFFont.COLOR_RED));
        items_style.setFont(celltbnamefont);
        items_style.setWrapText(true);
		
        for (int i = 0; i < name.length; i++) {
            HSSFCell cell = row.createCell(i);
            sheet.setColumnWidth(i, 5335);
            cell.setCellValue(name[i]);
            cell.setCellStyle(items_style);
        }

        HSSFCellStyle datestyle = workbook.createCellStyle();
        HSSFDataFormat df = workbook.createDataFormat();
        datestyle.setDataFormat(df.getFormat("yyyy-mm-dd"));

        int rowNum = 1;
        for (HashMap<String,Object> entry : table) {
            // create the row data   
//           String s	= entry.get("单据号/编码").toString();
//           int size = entry.size();
           entry.remove("ID_HEAD_ID");
           entry.remove("COMPANY_ID");
           entry.remove("DOC_STATUS");           
           entry.remove("OUTCODE");
           HSSFRow datarow = sheet.createRow(rowNum++);          
           Collection collection= entry.values();
           Iterator its = collection.iterator();
        	for(int i=0;i<entry.size();i++){ 
            	datarow.createCell(i).setCellValue(entry.get(name[i].toString()).toString());
            }           
        }
        

        
//        int rowNum = 1;
//        for (HashMap<String,Object> entry : table) {
//        	HSSFRow datarow = sheet.createRow(rowNum++);    
//        	String s= entry.get("发票种类").toString();
//        	datarow.createCell(0).setCellValue(entry.get("发票种类").toString());
//        	datarow.createCell(1).setCellValue(entry.get("单据号").toString());
//        	datarow.createCell(2).setCellValue(entry.get("单据日期").toString());
//        	datarow.createCell(3).setCellValue(entry.get("客户编号").toString());
//        	datarow.createCell(4).setCellValue(entry.get("客户名称").toString());
//        	datarow.createCell(5).setCellValue(entry.get("客户税号").toString());
//        	datarow.createCell(6).setCellValue(entry.get("客户地址电话").toString());
//        	datarow.createCell(7).setCellValue(entry.get("客户银行及帐号").toString());
//        	datarow.createCell(8).setCellValue(entry.get("备注").toString());
//        	datarow.createCell(9).setCellValue(entry.get("专用发票红票通知单号").toString());
//        	datarow.createCell(10).setCellValue(entry.get("普通发票红票对应正数发票代码").toString());
//        	datarow.createCell(11).setCellValue(entry.get("普通发票红票对应正数发票号码").toString());
//        	datarow.createCell(12).setCellValue(entry.get("开票人").toString());
//        	datarow.createCell(13).setCellValue(entry.get("复核人").toString());
//        	datarow.createCell(14).setCellValue(entry.get("收款人").toString());
//        	datarow.createCell(15).setCellValue(entry.get("销方银行及帐号").toString());
//        	datarow.createCell(16).setCellValue(entry.get("销方地址电话").toString());
//        	datarow.createCell(17).setCellValue(entry.get("商品编号").toString());
//        	datarow.createCell(18).setCellValue(entry.get("规格型号").toString());
//        	datarow.createCell(19).setCellValue(entry.get("计量单位").toString());
//        	datarow.createCell(20).setCellValue(entry.get("数量").toString());
//        	datarow.createCell(21).setCellValue(entry.get("金额(含税)").toString());
//        	datarow.createCell(22).setCellValue(entry.get("税率").toString());
//        	datarow.createCell(23).setCellValue(entry.get("税额").toString());
//        	datarow.createCell(24).setCellValue(entry.get("折扣金额(含税)").toString());
//        	datarow.createCell(25).setCellValue(entry.get("折扣税额").toString());
//        	
//        }
	}

}
